-- Start of DDL Script for Package PEMEX.PKG_GRAFICAS
-- Generated 08/01/2012 17:09:20 from PEMEX@XE

CREATE OR REPLACE 
PACKAGE pkg_graficas
  IS
 
    FUNCTION getFechasObra(PCVE_ESTATUS_OBRA IN VARCHAR2) RETURN sys_refcursor;
	procedure getObrasJefeSector(resultado out sys_refcursor);
	
  PROCEDURE getObrasporSolicitante (resultado out sys_refcursor);
  FUNCTION  getTotalSolicitudes RETURN sys_refcursor;
 
  FUNCTION getTotalObras RETURN sys_refcursor;
  FUNCTION getTotalAfectados RETURN sys_refcursor;
  PROCEDURE getTotalPrioritarias (resultado out sys_refcursor); 
  PROCEDURE getObrasJefeSectorPrio (resultado out sys_refcursor); 
  PROCEDURE getListadoObrasPorEstatus (resultado out sys_refcursor,status in varchar2);
END; -- Package spec
/



-- End of DDL Script for Package PEMEX.PKG_GRAFICAS

-- Start of DDL Script for Package Body PEMEX.PKG_GRAFICAS
-- Generated 08/01/2012 17:10:01 from PEMEX@XE

CREATE OR REPLACE 
PACKAGE BODY pkg_graficas
IS
    /* Resultados usados en la grafica de gant*/
    FUNCTION getFechasObra(PCVE_ESTATUS_OBRA IN VARCHAR2) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
	sqlCadena VARCHAR2(3000);
	sqlCondicion VARCHAR2(500);
    BEGIN
	sqlCondicion:=' where a.CVE_ESTATUS_OBRA = '||PCVE_ESTATUS_OBRA;
	
	sqlCadena:='SELECT  a.CVE_TRABAJO_REALIZAR,decode(a.cve_trabajo_realizar_padre,null,a.desc_obra,a.DESC_OBRA_COMPLEMENTARIA)DESCRIPCION,
                a.cve_trabajo_realizar_padre,a.supervisor,
                (SELECT TO_CHAR(nvl(MIN(FECHA_OBTENCION_PER_PASO),a.fecha_registro),''DD/MM/YY'') FROM SOLICITUD_AFECTADO WHERE CVE_TRABAJO_REALIZAR = A.CVE_TRABAJO_REALIZAR) MIN_PPO,
                (SELECT TO_CHAR(nvl(MAX(FECHA_OBTENCION_PER_PASO),a.fecha_registro),''DD/MM/YY'') FROM SOLICITUD_AFECTADO WHERE CVE_TRABAJO_REALIZAR = A.CVE_TRABAJO_REALIZAR) MAX_PPO,
                (SELECT TO_CHAR(nvl(MIN(FECHA_ENVIO_PERMISO_PASO),a.fecha_registro),''DD/MM/YY'') FROM SOLICITUD_AFECTADO WHERE CVE_TRABAJO_REALIZAR = A.CVE_TRABAJO_REALIZAR) MIN_PPE,
                (SELECT TO_CHAR(nvl(MAX(FECHA_ENVIO_PERMISO_PASO),a.fecha_registro),''DD/MM/YY'') FROM SOLICITUD_AFECTADO WHERE CVE_TRABAJO_REALIZAR = A.CVE_TRABAJO_REALIZAR) MAX_PPE,
				
				a.CVE_ESTATUS_OBRA,
				
                to_char(to_date(A.FECHA_LIMITE_SOLICITUD,''dd/mm/yy''),''dd/mm/yyyy'') FECHA_LIMITE_SOLICITUD,
                A.TOTAL_AFECTADOS
				
        		FROM
            		V_OBRAS A ';
					
	IF (PCVE_ESTATUS_OBRA IS NOT NULL AND LENGTH(PCVE_ESTATUS_OBRA)>0)THEN
	   sqlCadena:=sqlCadena ||sqlCondicion;
	END IF;
	
	 sqlCadena:=sqlCadena ||' ORDER BY a.fecha_registro asc';
     
		
    OPEN resultado FOR
    	 sqlCadena;

    return resultado;
    END;
    
	
    /* Resultados usados en la grafica de obras y monto pot jefe de sector */
    procedure getObrasJefeSector(resultado out sys_refcursor)
    IS
    BEGIN

    OPEN resultado FOR
		SELECT
    		    a.CVE_USUARIO
			   ,iniciales(A.NOMBRE||' '||A.APELLIDO_P||' '||A.APELLIDO_M) NOMBRE
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   )
			   ) TOTAL_OBRAS
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=1 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   )
			   ) OBRAS_SIN_DEFINIR_AFECT	   
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=2 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   )
			   ) OBRAS_SIN_ATENDER  	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=3 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   )
			   ) OBRAS_EN_PROCESO  	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=4 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   )
			   ) ATENDIDAS
			   ,(
			   			   SELECT SUM(Z.MONTO_POR_AFECTACION_APROBADO) FROM SOLICITUD_AFECTADO Z WHERE Z.CVE_TRABAJO_REALIZAR IN (
						   SELECT DISTINCT CVE_TRABAJO_REALIZAR FROM SOLICITUD_TRABAJOS_REALIZAR 
						   WHERE CVE_ESTATUS < 4
						   		 AND CVE_SOLICITUD IN (
						   		 	 SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO  )
						   )
			   ) MONTO_TOTAL_PENDIENTE	    	         
		FROM 
			   CAT_USUARIO A
		WHERE
			   A.CVE_ROL = 1	  
		order by  2 desc  ;
    END;	
    

    procedure getObrasporSolicitante (resultado out sys_refcursor)
    IS
    BEGIN

    OPEN resultado FOR
        	SELECT
        	           a.CVE_SOLICITANTE
        			   ,iniciales(A.DESCRIPCION) DESCRIPCION
        			   ,(
        			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE CVE_SOLICITUD IN (
        						   SELECT DISTINCT CVE_SOLICITUD FROM SOLICITUD WHERE CVE_SOLICITANTE = A.CVE_SOLICITANTE
        						   )
        			   ) TOTAL_OBRAS
                        ,(
        			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=1 AND Z.CVE_SOLICITUD IN (
        						   SELECT DISTINCT CVE_SOLICITUD FROM SOLICITUD WHERE CVE_SOLICITANTE = A.CVE_SOLICITANTE
        						   )
        			   ) OBRAS_SIN_DEFINIR_AFECT	
        			   ,(
        			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=2 AND Z.CVE_SOLICITUD IN (
        						   SELECT DISTINCT CVE_SOLICITUD FROM SOLICITUD WHERE CVE_SOLICITANTE = A.CVE_SOLICITANTE
        						   )
        			   ) OBRAS_SIN_ATENDER  	
        			   ,(
        			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=3 AND Z.CVE_SOLICITUD IN (
        						   SELECT DISTINCT CVE_SOLICITUD FROM SOLICITUD WHERE CVE_SOLICITANTE = A.CVE_SOLICITANTE
        						   )
        			   ) OBRAS_EN_PROCESO  	
        			   ,(
        			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=4 AND Z.CVE_SOLICITUD IN (
        						   SELECT DISTINCT CVE_SOLICITUD FROM SOLICITUD WHERE CVE_SOLICITANTE = A.CVE_SOLICITANTE
        						   )
        			   ) ATENDIDAS
        			   ,(
        			   			   SELECT SUM(Z.MONTO_POR_AFECTACION_APROBADO) FROM SOLICITUD_AFECTADO Z WHERE Z.CVE_TRABAJO_REALIZAR IN (
        						   SELECT DISTINCT CVE_TRABAJO_REALIZAR FROM SOLICITUD_TRABAJOS_REALIZAR
        						   WHERE CVE_SOLICITUD IN (
        						   		 	 SELECT DISTINCT CVE_SOLICITUD FROM SOLICITUD WHERE CVE_SOLICITANTE = A.CVE_SOLICITANTE  )
        						   )
        			   ) MONTO_TOTAL_PENDIENTE	    			  	
        		FROM
        			   CAT_SOLICITANTE A
        ORDER BY 3;

    END;


    /* Usadas para mostrar el cilindro*/
    FUNCTION getTotalSolicitudes RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN		
    OPEN resultado FOR
    	 select 
    	       (select COUNT(*) from SOLICITUD)                      TOTAL_SOLICITUDES
    	      ,(select COUNT(*) from SOLICITUD WHERE CVE_ESTATUS=1)  TOTAL_ATENDIDAS
         from dual;
         
    return resultado;
    END;

    /* Usadas para mostrar el cilindro*/
    FUNCTION getTotalObras RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN		
    OPEN resultado FOR
    	 select
    	       (select COUNT(*) from SOLICITUD_TRABAJOS_REALIZAR) TOTAL_OBRAS
    	      ,(select COUNT(*) from SOLICITUD_TRABAJOS_rEALIZAR WHERE CVE_ESTATUS = 4)  TOTAL_ATENDIDAS
         from dual;

    return resultado;
    END;

    /* Usadas para mostrar el cilindro*/
    FUNCTION getTotalAfectados RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN		
    OPEN resultado FOR
    	 select
    	       (select count(*) from SOLICITUD_AFECTADO A ) TOTAL_AFECTADOS
    	      ,(select count(*) from SOLICITUD_AFECTADO A WHERE   A.fecha_obtencion_per_paso IS NOT NULL)  TOTAL_ENVIADOS
         from dual;

    return resultado;
    END;
    
    procedure getTotalPrioritarias (resultado out sys_refcursor)
    IS
    BEGIN		
    OPEN resultado FOR
	SELECT
			   (
			   	 SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE UPPER(ESPRIORITARIA) =UPPER('S')
						
			   ) TOTAL_OBRAS
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=1 AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) OBRAS_SIN_DEFINIR_AFECT	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=2 AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) OBRAS_SIN_ATENDER  	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=3 AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) OBRAS_EN_PROCESO  	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=4 AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) ATENDIDAS    	
		FROM  dual;
    END;


 PROCEDURE getObrasJefeSectorPrio (resultado out sys_refcursor)
    IS
    BEGIN

    OPEN resultado FOR
		SELECT
    		    a.CVE_USUARIO
			   ,iniciales(A.NOMBRE||' '||A.APELLIDO_P||' '||A.APELLIDO_M) NOMBRE
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   ) AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) TOTAL_OBRAS
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=1 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   ) AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) OBRAS_SIN_DEFINIR_AFECT	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=2 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   ) AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) OBRAS_SIN_ATENDER  	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=3 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   ) AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) OBRAS_EN_PROCESO  	
			   ,(
			   			   SELECT COUNT(*) FROM SOLICITUD_TRABAJOS_REALIZAR Z WHERE Z.CVE_ESTATUS=4 AND Z.CVE_SOLICITUD IN (
						   SELECT DISTINCT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO = A.CVE_USUARIO
						   ) AND UPPER(ESPRIORITARIA) =UPPER('S')
			   ) ATENDIDAS
			   	    	
		FROM
			   CAT_USUARIO A
		WHERE
			   A.CVE_ROL = 1	
		order by  2 desc  ;

    END;	 
    /*
        Recibe una Cadena indicando el estatus que se desea consultar
    */
    PROCEDURE getListadoObrasPorEstatus (resultado out sys_refcursor,status in varchar2)
    IS
    BEGIN		
    
    IF(IsNumber(status)=TRUE) THEN
        BEGIN
            OPEN resultado FOR
            SELECT * FROM v_obras WHERE CVE_ESTATUS_OBRA = status;
        END;
    ELSE    
           IF (status = 'PENDIENTES') THEN
                OPEN resultado FOR
                SELECT * FROM v_obras WHERE CVE_ESTATUS_OBRA < 4;
                
            ELSIF (status = 'TERMINADAS' OR status = 'COMPLETADAS') THEN
                OPEN resultado FOR
                SELECT * FROM v_obras WHERE CVE_ESTATUS_OBRA = 4;        
            END IF;    
    END IF;        
    END;           	
END;
/



-- End of DDL Script for Package Body PEMEX.PKG_GRAFICAS
